#!/usr/bin/env python
# -*- coding: utf-8 -*-
import sqlite3
import datetime
import os
import pwd
import getpass
import smtplib
from email.mime.text import MIMEText
from optparse import OptionParser

str_status_closed="cerrado"
str_status_open="abierto"
str_project_default="sysadmin"
str_user_default="hector"
db="stdb"

mail_server="localhost"
mail_footer="\n\nSaludos,\n\nTu amigable vecino El Sistema de Tareas (st)"
mail_admin="st@example.com"
task_user="hector"

color_green=";32m"
color_green_normal=";92m"
color_red=";31m"
color_blue=";34m"
color_yellow=";33m"
color_default="m"

login=pwd.getpwuid(os.getuid())[0]
today= datetime.date.today()

def get_email(user):
    u=(user,)
    c.execute('select email from user where user=(?)',u)
    cnx.commit()
    user=c.fetchone()
    return user[0]

def send_mail(mail_from,mail_to,mail_subject,mail_text):
    #mail_message = MIMEText(mail_text, _charset='utf-8')
    if mail_to != "":
       mail_message = """\
From: %s
To: %s
Subject: %s

%s

""" % (mail_from, ", ".join(mail_to), mail_subject,mail_text)
       server = smtplib.SMTP(mail_server)
       server.sendmail(mail_from, mail_to, mail_message)
       server.quit()

def new_task(task,mail_sender=""):
    print "Creando nueva tarea ..."
    c.execute('insert into task values (?,?,?,?,?,?,?)',task)
    cnx.commit()
    c.execute('select last_insert_rowid()')
    cnx.commit()
    id = c.fetchall()[0][0]
    print "Se ha creado la tarea #"+str(id)
    #mail_from=mail_admin
    #mail_to=[get_email(task[2])]
    #mail_subject="[DEV] Tarea #"+str(id)
    #mail_text=task[1]
    #if task[6] !="":
    #   mail_text=mail_text+"\n\n"+task[6]
    #if mail_sender!="":
    #   mail_text=mail_text+"\n\nEsta tarea ha sido generada por: "+mail_sender[0]
    #mail_text=mail_text+mail_footer
    #send_mail(mail_from,mail_to,mail_subject,mail_text)
    return id

def mail_task_today():
    c.execute('select * from user')
    cnx.commit()
    users=c.fetchall()
    for user in users:
	mail_text=""
        #Lista de tareas programadas
        t=(user[1],today,)
        c.execute('select id,message,project from task where user=(?) AND status=(?)',t)
        cnx.commit()
        tasks=c.fetchall()
	if len(tasks)>0:
           mail_text=mail_text+"Tareas Programadas:\n"
	   for task in tasks:
	       mail_text=mail_text+"* "+task[1]+" +"+task[2]+"\n"

        #Lista de tareas pendientes
        t=(user[1],str_status_open,)
        c.execute('select id,message,project from task where user=(?) AND status=(?)',t)
        cnx.commit()
        tasks=c.fetchall()
	if len(tasks)>0:
           mail_text=mail_text+"Tareas Pendientes:\n"
	   for task in tasks:
	       mail_text=mail_text+"* "+task[1]+" +"+task[2]+"\n"

        #Si no hay tareas pendientes el sistema no te hace spam 
        if len(mail_text)>0:
           mail_text=mail_text+mail_footer
           mail_from=user[2]
           mail_to=[user[2]]
           mail_subject="[DEV] Lista de Tareas para Hoy "+str(today)
           send_mail(mail_from,mail_to,mail_subject,mail_text)

def mail_task_done():
    u=(login,str_status_closed,today,)
    sql='select id,message,project from task WHERE user=(?) AND status=(?) AND date(date)=(?) ORDER BY date DESC'
    c.execute(sql,u) 
    cnx.commit()
    tasks=c.fetchall()
    mail_text=""
    if len(tasks)>0:
       mail_text=mail_text+"Tareas Realizadas:\n"
       for task in tasks:
           mail_text=mail_text+"* "+task[1]+" +"+task[2]+"\n"
    if len(mail_text)>0:
       mail_text=mail_text+mail_footer
       mail_from=mail_admin
       mail_to=[get_email(login)]
       mail_subject="[DEV] Tareas Realizadas Hoy "+str(today)
       send_mail(mail_from,mail_to,mail_subject,mail_text)
	
def close_task(task_id):
    t=(str_status_closed,datetime.datetime.now(),task_id,)
    c.execute('UPDATE task SET status=(?), date=(?)  WHERE id=(?)',t)
    cnx.commit()
    print "Se ha cerrado la tarea #"+str(task_id)

def open_task(task_id):
    t=(str_status_open,task_id,)
    c.execute('UPDATE task SET status=(?) WHERE id=(?)',t)
    cnx.commit()
    print "Se ha abierto la tarea #"+str(task_id)

def get_task(task_id):
    t=(task_id,)
    c.execute('select * from task where id=(?)',t)
    cnx.commit()
    task=c.fetchone()
    return task  

def update_task(task):
    c.execute('UPDATE task SET message=(?), user=(?), status=(?), project=(?), date=(?) WHERE id=(?)',task)
    cnx.commit()
    print "Se ha actualizado la tarea #"+str(task[5])

def print_task(task,color=color_default):
    status="["+str(task[3])+"]"
    user="@"+str(task[2])
    project="+"+str(task[4])
    tid=str(task[0])
    message=str(task[1])
    print chr(27)+"[0"+color+"#"+tid+"\t"+message+"\t\t"+project+"\t"+user+"\t"+status+chr(27)+"[0m"

def print_task_verbose(task,color=color_default):
    status="["+str(task[3])+"]"
    user="@"+str(task[2])
    project="+"+str(task[4])
    tid=str(task[0])
    message=str(task[1])
    description=str(task[6])
    print chr(27)+"[0"+color+"#"+tid+"\t"+message+"\t\t"+project+"\t"+user+"\t"+status+chr(27)+"[0m"
    if not description=="":
       print description

def print_task_sql(sql,u,color):
    c.execute(sql,u)
    cnx.commit()
    tasks=c.fetchall()
    for t in tasks:
        print_task(t,color)

def print_task_sql_verbose(sql,u,color):
    c.execute(sql,u)
    cnx.commit()
    tasks=c.fetchall()
    for t in tasks:
        print_task_verbose(t,color)

def print_task_header():
    print "#ID\tTITLE\t\t+PROJECT\t@USER\t[status]"

if __name__ == "__main__":
   usage = """usage: %prog command [options]\n
List of Commands:
open\t(re)Open a task
close\tClose a task
update\tUpdate information about a task
list\tList tasks
install\tInstall database 
help\tDisplay a helpful usage message"""
   version = "Simple Tracker %prog 0.5"
   parser = OptionParser(usage=usage,version=version)
    
   parser.add_option('-u', '--user', dest="user", help=u"user assigned to the task")
   parser.add_option('-p', '--project', dest="project", help=u"project of the task")
   parser.add_option('-m', '--message', dest="message", help=u"task description")
   parser.add_option('-s', '--status', dest="status", help=u"task status")
   parser.add_option('-t', '--task', dest="task", help=u"task code")
   parser.add_option('-l', '--list', dest="list", help=u"type of list")
   parser.add_option('-r', '--report', dest="report", help=u"type of report")

   options, args = parser.parse_args()


   if len(args)==1:
      #conexion a la base de datos - falta try/except
      cnx=sqlite3.connect(db)
      cnx.text_factory = str
      c=cnx.cursor()
      arg=args[0]
      if arg=="open":
         if options.task:
            open_task(options.task)
         else: 
	    if options.message:
	       if not options.user:
	          options.user=login
	       if not options.project:
                  options.project=str_project_default
               if not options.status:
                  options.status=str_status_open
               task=(None,options.message,options.user,options.status,options.project,datetime.datetime.now(),"")
               new_task(task)  
            else:
               parser.error("Se requiere un codigo de tarea (-t) o una descripcion (-m)")
      elif arg=="close":
	 if options.task:
            close_task(options.task) 
         else:
            if options.message:
	       if not options.user:
	          options.user=login
	       if not options.project:
                  options.project=str_project_default
               task=(None,options.message,options.user,str_status_closed,options.project,datetime.datetime.now(),"")
               new_task(task)  
            else:
               parser.error("Se requiere un codigo de tarea (-t) o una descripcion (-m)")
      elif arg=="describe":
	 if options.task:
            task=get_task(options.task)
            print_task_header()
            u=(options.task,)
            sql='select * from task where id=(?)'
            print_task_sql_verbose(sql,u,color_red)
      elif arg=="update":
	 if options.task:
            task=get_task(options.task)
	    if not options.message:
               message=task[1]
	    else:
               message=options.message
            if not options.user:
               user=task[2]
            else:
               user=options.user
            if not options.status:
               status=task[3]
            else:
               status=options.status
            if not options.project:
               project=task[4]
            else:
               project=options.project
	    date=datetime.datetime.now()
            if not (user==task[2]):
               mail_from=mail_admin
    	       mail_to=[get_email(user)]
               mail_subject="[DEV] Tarea #"+str(task[0])
	       mail_text="Se te ha asignado la tarea:\n\n"
               mail_text=mail_text+"- "+message
               mail_text=mail_text+mail_footer
               send_mail(mail_from,mail_to,mail_subject,mail_text)
            task=(message,user,status,project,date,options.task)
            update_task(task)
         else:
            parser.error("Se requiere un codigo de tarea (-t)")
      elif arg=="list":
	   if options.list:
	      if options.list=="project":
           	   c.execute('select project from task group by project')
                   cnx.commit()
           	   projects=c.fetchall()
		   print "+PROJECT"
           	   for p in projects:
	       	       print "+"+p[0]
	      elif options.list=="today":
           	print_task_header()
           	u=(today,)
    		sql='select * from task where status=(?)'
                print_task_sql(sql,u,color_blue)
	      elif options.list=="all":
           	print_task_header()
		u=()
    		sql='select * from task ORDER BY date DESC'
                print_task_sql(sql,u,color_yellow)
	      elif options.list=="done":
           	print_task_header()
		u=(login,str_status_closed,today,)
    		sql='select * from task WHERE user=(?) AND status=(?) AND date(date)=(?) ORDER BY date DESC'
                print_task_sql(sql,u,color_yellow)
	   elif options.user:
           	print_task_header()
           	u=(options.user,str_status_open,)
    		sql='select * from task where user=(?) and status=(?)'
                print_task_sql(sql,u,color_red)
           else:
           	print_task_header()
           	u=(login,str_status_open,)
    		sql='select * from task where user=(?) and status=(?)'
                print_task_sql(sql,u,color_red)
    		sql='select * from task where user<>(?) and status=(?)'
                print_task_sql(sql,u,color_green)
           	#u=(os.getlogin(),today,)
           	#u=(login,)
           	u=()
           	#sql='select * from task where user=(?) and status like \'2010%\'  ORDER BY status LIMIT 5'
           	sql='select * from task where status like \'2010%\'  ORDER BY status LIMIT 5'
                #print_task_sql(sql,u,color_blue)
                print_task_sql(sql,u,color_blue)
           	u=(str_status_closed,)
           	sql='select * from task where status=(?) ORDER BY date DESC LIMIT 10'
                print_task_sql(sql,u,color_yellow)
      elif arg=="install":
           c.execute("CREATE TABLE task (id INTEGER PRIMARY KEY,message,user,status,project,date,description TEXT)")
           c.execute("CREATE TABLE user (uid INTEGER PRIMARY KEY,user,email)")
           cnx.commit()
           print "Instalacion OK"
      elif arg=="report":
	   if options.report:
	      if options.report=="done":
                 mail_task_done()
           else:
              mail_task_today()
      elif arg=="mail":
           import mailbox
           task_mbox=mailbox.mbox('/var/spool/mail/'+task_user)
           for msg in task_mbox:
               subject = msg.get('Subject')
	       body = msg.get_payload()	
	       mail_to = [msg.get('From')]
	       data=subject.split('@')
	       message=data[0]
	       if len(data)<2:
                  user=str_user_default
	       else:
                  user=data[1]
               task=(None,message,user,str_status_open,str_project_default,datetime.datetime.now(),body)
               task_id=new_task(task,mail_to) 
               mail_from=mail_admin
               mail_subject="[DEV] Tarea #"+str(task_id)
	       mail_text="Se ha asignado la tarea: "+message+", al usuario "+user+"\n\n"
               mail_text=mail_text+""+body
               mail_text=mail_text+mail_footer
               send_mail(mail_from,mail_to,mail_subject,mail_text)
           task_mbox.clear()
           task_mbox.flush()
           os.system('chown '+task_user+' /var/spool/mail/'+task_user)
      elif arg=="help":
         parser.print_help()
      else:
         parser.print_help()
	 parser.error("Argumento invalido")
   else:
      parser.print_help()
      parser.error("Numero incorrecto de argumentos")
      
